package com.aote.model;

import java.util.Hashtable;
import java.util.Iterator;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;

import com.aote.component.format.FormatFactory;
import com.browsesoft.ExtendElement;
import com.browsesoft.dbtools.DBTools;
import com.browsesoft.oa.HTMLSqlQueryModel;
import com.browsesoft.tools.XMLTool;

public class HTMLPageSqlQueryModel extends HTMLSqlQueryModel {
	private HttpServletRequest srequest;

	/**
	 * 一次取多少行数据
	 */
	private int number = 100;

	/**
	 * 缓存中第一行数据的行号
	 */
	private int rownum = 0;

	/**
	 * 合计结果
	 */
	private Map sums;

	public void init(HttpServletRequest request) throws Exception {
		super.init(request);
		Element element = this.component.getElement();
		if (element.hasAttribute("number")) {
			number = Integer.parseInt(element.getAttribute("number"));
		}
	}

	public void setDatas(HttpServletRequest request) {
		this.srequest = request;
		String action = request.getParameter("action");
		if (action != null && action.equals("search")) {
			this.rownum = 0;
			this.datas.clear();
		}
	}

	protected Hashtable getRowData(int row) {
		if (row - rownum >= this.datas.size() || row < rownum) {
			this.getOneTimeData(row);
		}
		return (Hashtable) this.datas.get(row - rownum);
	}

	/**
	 * 如果所取数据超出缓存，取一次数据，并将缓存中的行号更新
	 * 
	 * @param num
	 */
	private void getOneTimeData(int num) {
		try {
			this.rownum = num;
			String sql = (String) getSqls(srequest, this.sqlElements,
					this.component).get(0);
			sql = sql + " limit " + Integer.toString(rownum) + ","
					+ Integer.toString(number);
			System.out.println("取数据－" + num + ":" + sql);
			String[][] strs = DBTools.executeQueryWithTableHead(sql);
			this.datas = this.parseResult(strs);
		} catch (RuntimeException re) {
			throw re;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 得到总行数以及需要合计的内容，需要合计的内容直接放在合计变量sum里
	 * 
	 * @return 总行数
	 */
	public int getRowCount() {
		try {
			String sql = (String) getSqls(srequest, this.sqlElements,
					this.component).get(0);
			// 找到sum的字段
			Element sqlE = (Element) this.sqlElements.get(0);
			String sumSql = sqlE.getAttribute("sum");
			if (sql.indexOf("group") != -1) {
				if (sumSql != null && !sumSql.equals("")) {
					sql = "select count(*) c," + sumSql + " from (" + sql
							+ ") a";
				} else {
					sql = "select count(*) from (" + sql + ") a";
				}
			} else {
				if (sumSql != null && !sumSql.equals("")) {
					sql = "select count(*) c," + sumSql + " "
							+ sql.substring(sql.indexOf("from"), sql.length());
				} else {
					sql = "select count(*) "
							+ sql.substring(sql.indexOf("from"), sql.length());
				}
			}
			System.out.println(sql);
			String[][] strs = DBTools.executeQueryWithTableHead(sql);
			// 设置合计
			if (sumSql != null && !sumSql.equals("")) {
				this.sums = DBTools.getAttributes(strs, 1);
			}
			return Integer.parseInt(strs[1][0]);
		} catch (RuntimeException re) {
			throw re;
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	public void modifyAddupCell(Document doc, Element td, int col)
			throws Exception {
		Column column = (Column) this.columns.get(col);
		String str = " ";
		// 有小计标识,得到小计值
		String addup = column.addup;
		if (addup != null && addup.equals("addup")) {
			str = (String) this.getSum(column, td, this.start, this.end);
			str = FormatFactory.getInstance().format(str, td);
		}
		Node node = XMLTool.createTextNode(doc, str);
		// 替换单元格
		ExtendElement.replaceElementContext(td, node);
	}

	public void modifySumCell(Document doc, Element td, int col)
			throws Exception {
		Column column = (Column) this.columns.get(col);
		String str = " ";
		// 有小计标识,得到小计值
		String addup = column.addup;
		if (addup != null && addup.equals("addup")) {
			str = getSum(column, td);
			str = FormatFactory.getInstance().format(str, td);
		}
		Node node = XMLTool.createTextNode(doc, str);
		// 替换单元格
		ExtendElement.replaceElementContext(td, node);
	}

	/**
	 * 得到某列合计
	 */
	private String getSum(Column column, Element td) {
		String name = column.field;
		String result = (String) sums.get(name);
		return result;
	}

	/**
	 * 得到某列小计值,从start到end项,如果到最后一项,可以让end=Integer.MAX_VALUE
	 */
	protected String getSum(Column column, Element td, int start, int end) {
		// 针对当前缓存修正start及end
		start = start - this.rownum;
		end = end - this.rownum;
		String name = column.field;
		// 取得实体
		Iterator iter = this.datas.iterator();
		double sum = 0;
		// 对于每一个
		for (int i = 0; i < end && iter.hasNext(); i++) {
			Hashtable data = (Hashtable) iter.next();
			// 开始行前面的,不计算
			if (i < start) {
				continue;
			}
			try {
				// 将该列的值累加
				sum += Double.parseDouble((String) data.get(name));
			} catch (Exception e) {
			}
		}
		Element span = column.element;
		String str = FormatFactory.getInstance().format(sum + "", span);
		return str;
	}
}
